/**
 * Copyright 2011 Google Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.google.api.adwords.starterkit.db.tables;

import com.google.api.adwords.starterkit.db.keys.KeywordParamKey;
import com.google.api.adwords.starterkit.db.structures.KeywordParamStructure;
import com.google.api.adwords.starterkit.utils.DumpExceptions;

import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;

/**
 * The Class KeywordParamUtils encapsulates most of KeywordParam DB access.
 * 
 * @author rodrigo.f.vale@gmail.com  (Rodrigo Vale)
 */
public class KeywordParamTable {
  private PreparedStatement prepReplace;
  private PreparedStatement prepLoad;
  private PreparedStatement prepUpdated;
  private PreparedStatement prepExists;
  private Connection conn;
  private PreparedStatement prepResetIsUpdated;
  private static final Logger log = Logger.getLogger("StarterKit");
  
  /**
   * Instantiates a new keyword param utils.
   *
   * @param conn the DB connection
   */
  public KeywordParamTable(Connection conn) {
    this.conn = conn;
    try { 
      Statement stat = conn.createStatement();      
      stat.executeUpdate(
          "CREATE TABLE IF NOT EXISTS KeywordParams "
        + "  ( "
        + "     LocalId        INTEGER PRIMARY KEY, "
        + "     KeywordId      LONG DEFAULT 0, "
        + "     LocalAdGroupId LONG DEFAULT 0, "
        + "     AdGroupId      LONG DEFAULT 0, "
        + "     AdParam1       STRING, "
        + "     AdParam2       STRING, "
        + "     IsUpdated      BOOLEAN  DEFAULT 0"
        + "  );");          
      stat.executeUpdate(
          "CREATE INDEX IF NOT EXISTS KeywordParamsLocalAdGroupIdLocalIdIndex " 
        + "on KeywordParams (LocalAdGroupId, LocalId);");
      stat.executeUpdate(
          "CREATE INDEX IF NOT EXISTS KeywordParamsKeywordIdAdGroupIdIndex " 
        + "on KeywordParams (KeywordId, AdGroupId);");            
      
      prepExists = conn.prepareStatement(
          "SELECT LocalId FROM KeywordParams WHERE LocalId = ? " 
        + "AND LocalAdGroupId = ?;"); 
      prepLoad = conn.prepareStatement(
          "SELECT * FROM KeywordParams WHERE LocalId = ? " 
        + "AND LocalAdGroupId = ?"); 
      prepUpdated = conn.prepareStatement(
          "UPDATE KeywordParams SET IsUpdated = ? WHERE LocalId = ? " 
        + "AND LocalAdGroupId = ?;"); 
      prepReplace = conn.prepareStatement(
          "REPLACE INTO KeywordParams (LocalId, KeywordId, LocalAdGroupId, " 
        + "AdGroupId, AdParam1, AdParam2, IsUpdated) "
        +  "VALUES (?, ?, ?, ?, ?, ?, ?);");    
      prepResetIsUpdated = conn.prepareStatement(
          "UPDATE KeywordParams SET IsUpdated = ?");
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }    
  }

  /**
   * Verify if a KeywordParam Exists.
   *
   * @param kwdParamKey the primary key
   * @return true, if successful
   */
  public boolean exists(KeywordParamKey kwdParamKey) {
    boolean exists = false;
    ResultSet rs = null;
    try {
      prepExists.setInt(1, kwdParamKey.getLocalId());      
      prepExists.setInt(2, kwdParamKey.getLocalAdGroupId());
      rs = prepExists.executeQuery();
      if (rs.next()) {
        exists = true;
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return exists;
  }

  /**
   * Gets the KeywordParamStructure from a generic SQL request.
   *
   * @param rs the ResultSet of a generic SQL request
   * @return all fields managed by KeywordParam wrapper 
   */
  public KeywordParamStructure getResultSet(ResultSet rs) {
    KeywordParamStructure kwdParamStruct = new KeywordParamStructure();
    try {
      kwdParamStruct.setKeywordId(rs.getLong("KeywordId"));
      kwdParamStruct.setLocalId(rs.getInt("LocalId"));
      kwdParamStruct.setAdGroupId(rs.getLong("AdGroupId"));
      kwdParamStruct.setLocalAdGroupId(rs.getInt("LocalAdGroupId"));
      kwdParamStruct.setParam1(rs.getString("AdParam1"));
      kwdParamStruct.setParam2(rs.getString("AdParam2"));
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
    return kwdParamStruct;
  }

  /**
   * Load a specific KeywordParam from DB.
   *
   * @param kwdParamKey the primary key
   * @return the keyword param structure
   */
  public KeywordParamStructure get(KeywordParamKey kwdParamKey) {
    KeywordParamStructure keywordParamStructure = null;
    ResultSet rs = null;
    try {
      prepLoad.setInt(1, kwdParamKey.getLocalId());
      prepLoad.setInt(2, kwdParamKey.getLocalAdGroupId());
      rs = prepLoad.executeQuery();
      if (rs.next()) {
        keywordParamStructure = getResultSet(rs);
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return keywordParamStructure;
  }

  /**
   * Save a specific KeywordParam to DB.
   *
   * @param kwdParamStruct the kwd param struct
   * @param hasUpdate the has update
   * @return the keyword param structure
   */
  public KeywordParamStructure save(KeywordParamStructure kwdParamStruct, 
      boolean hasUpdate) {
    ResultSet rs = null;
    try {
      prepReplace.setInt(1, kwdParamStruct.getLocalId());
      prepReplace.setLong(2, kwdParamStruct.getKeywordId());
      prepReplace.setInt(3, kwdParamStruct.getLocalAdGroupId());
      prepReplace.setLong(4, kwdParamStruct.getAdGroupId());
      prepReplace.setString(5, kwdParamStruct.getParam1());
      prepReplace.setString(6, kwdParamStruct.getParam2());
      prepReplace.setBoolean(7, hasUpdate);
      prepReplace.executeUpdate();
      rs = prepReplace.getGeneratedKeys();      
      kwdParamStruct.setLocalId(rs.getInt(1));    
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return kwdParamStruct;
  }

  /**
   * Informs to StarterKit that the keywordParam was updated by the application.
   * During the AdWords synchronization process only updated keywordParam will 
   * be processed by StarterKit. 
   *
   * @param kwdParamKey the keywordParam primary key
   * @param updated boolean flag 
   */
  public void setUpdated(KeywordParamKey kwdParamKey, boolean updated) {
    try {
      prepUpdated.setBoolean(1, updated);
      prepUpdated.setInt(2, kwdParamKey.getLocalId());
      prepUpdated.setLong(3, kwdParamKey.getLocalAdGroupId());
      prepUpdated.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }

  /**
   * Reset all KeywordParam flags to its default values 
   */
  public void resetStatus() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement("UPDATE KeywordParams SET " +
          "IsUpdated = 0 WHERE IsUpdated = 1;");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }        
  }

  /**
   * Update ad groups ids at keyword table.
   */
  public void updateAdGroupsIds() {
    log.debug("Updating AdGroupId on new KeywordParams");
    HashMap<Integer, Long> adGroupsIdMap = getNewAdGroupsIds();
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "UPDATE KeywordParams SET AdGroupId = ? " 
          + "WHERE LocalAdGroupId = ? AND AdGroupId = 0");
      int count = 0;
      for (int localAdGroupId : adGroupsIdMap.keySet()) {
        long adGroupId = adGroupsIdMap.get(localAdGroupId);
        prep.setLong(1, adGroupId);
        prep.setLong(2, localAdGroupId);
        prep.addBatch();
        count++;
        if (count == 100000) {
          count = 0;
          prep.executeBatch();
        }
      }
      prep.executeBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }         
  }
  
  /**
   * Update ad groups ids at keyword table.
   */
  public void updateKeywordsIds() {
    log.debug("Updating KeywordId for new KeywordParams");
    HashMap<Integer, Long> keywordsIdMap = getNewKeywordsIds();
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "UPDATE KeywordParams SET KeywordId = ? " 
          + "WHERE LocalId = ? AND KeywordId = 0");
      int count = 0;
      for (int localId : keywordsIdMap.keySet()) {
        long keywordId = keywordsIdMap.get(localId);
        prep.setLong(1, keywordId);
        prep.setInt(2, localId);
        prep.addBatch();
        count++;
        if (count == 100000) {
          count = 0;
          prep.executeBatch();
        }
      }
      prep.executeBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }         
  }
  
  /**
   * Gets the adgroup ids map.
   *
   * @return the ad group ids map
   */
  public HashMap<Integer, Long> getNewAdGroupsIds() {
    log.debug("Loading AdGroups Ids needed by new KeywordParams");
    HashMap<Integer, Long> adGroupIdsMap = new HashMap<Integer, Long>(); 
    ResultSet rs = null;
    try {
      Statement stat = conn.createStatement();
      rs = stat.executeQuery(
            "SELECT ag.LocalId, ag.AdGroupId FROM AdGroups ag, " 
          + "KeywordParams kp WHERE kp.LocalAdGroupId = ag.LocalId "
          + "and kp.AdGroupId = 0;");
      while (rs.next()) {
        adGroupIdsMap.put(rs.getInt("LocalId"), rs.getLong("AdGroupId"));
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return adGroupIdsMap;
  }
  
  /**
   * Gets the adgroup ids map.
   *
   * @return the ad group ids map
   */
  public HashMap<Integer, Long> getNewKeywordsIds() {
    log.debug("Loading Keywords Ids needed by new KeywordParams");
    HashMap<Integer, Long> kewordIdsMap = new HashMap<Integer, Long>(); 
    ResultSet rs = null;
    try {
      Statement stat = conn.createStatement();
      rs = stat.executeQuery(
            "SELECT kw.LocalId, kw.KeywordId FROM Keywords kw, "
          + "KeywordParams kp WHERE kp.LocalId = kw.LocalId "
          + "and kp.KeywordId = 0");
      while (rs.next()) {
        kewordIdsMap.put(rs.getInt("LocalId"), rs.getLong("KeywordId"));
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return kewordIdsMap;
  }  
 
  /**
   * Return all KeywordParam modified during the last Starter Kit execution
   * 
   * @return the ResultSet that contains KeywordParams modified 
   */
  public ResultSet getModified() {
    PreparedStatement prep = null;
    ResultSet rs = null;
    try {
      prep = conn.prepareStatement(
          "SELECT * FROM KeywordParams WHERE IsUpdated = 1");
      rs = prep.executeQuery();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
    return rs;
  }

  /**
   * Resets IsUpdated flag in all rows of AdGroups table
   */
  public void resetIsUpdated(boolean isUpdated) {
    try {
      prepResetIsUpdated.setBoolean(1, isUpdated);
      prepResetIsUpdated.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }     
  }  
}